//
//  FMDBManager.m
//  Account
//
//  Created by wanmeizty on 2016/11/29.
//  Copyright © 2016年 wanmeizty. All rights reserved.
//

#import "FMDBManager.h"

@implementation FMDBManager

+ (FMDBManager *)shareManager{
    
    static FMDBManager * manager;
    
    static dispatch_once_t onceToken;

    dispatch_once(&onceToken, ^{
        
        manager = [[FMDBManager alloc] init];
        
    });

    return manager;
    
}


- (instancetype)init{
    
    if (self = [super init]) {
        
        
        
    }
    
    return self;
    
}


//- (FMDatabaseQueue *)fmQueue:(NSString *)dbName{
//    
//    if (_fmQueue == nil) {
//        
//
//        //获取数据库路径，通常保存到沙盒中
//        NSString *filePath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:dbName];
//        
//        NSLog(@"%@",filePath);
//        
//        _fmQueue = [FMDatabaseQueue databaseQueueWithPath:filePath];
//
//
//        
//    }
//    
//    return _fmQueue;
//    
//}


// 创建数据库
- (void)createFMDBDataBaseWithDbName:(NSString *)dbName{
    
    //获取数据库路径，通常保存到沙盒中
    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:dbName];
    
    NSLog(@"%@",dbPath);
    
    //创建数据库实例 db 这里说明下:如果路径中不存在"Test.db"的文件,sqlite会自动创建"Test.db"

    _dataBase = [FMDatabase databaseWithPath:dbPath] ;
    if (![_dataBase open]) {
        NSLog(@"Could not open db.");
        return ;
    }else{
        NSLog(@"数据库打开");
    }

}

// 创建表
- (void)createTableWithTableName:(NSString *)tableName{
    
    if (![_dataBase open]) {
        NSLog(@"Could not open db.");
        return ;
    }else{
        NSLog(@"数据库打开");
        // 建表语句
        NSString * createTableSql = [NSString stringWithFormat:@"create table if not exists account (accountId integer primary key autoincrement, name text not null, fee text not null,date text, desc text,joinperson text)"];
        // [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text not null, age integer)"];
        //创建一个名为User的表，有两个字段分别为string类型的Name，integer类型的 Age
        
        if ([_dataBase executeUpdate:createTableSql]) {
            
            NSLog(@"创建表成功");
            
        }else{
            
            return ;
        }
    }
    
    
   
}

// 插入
- (BOOL)insertWithAccountModel:(AccountModel *)accountModel{
    
    if ([_dataBase open]) {
        // name , fee ,date , desc 
        NSString *sql1 = [NSString stringWithFormat:
                          @"INSERT INTO account (name, fee ,date , desc, joinperson ) VALUES ('%@', '%@', '%@', '%@' ,'%@')",
                          accountModel.name,accountModel.fee,accountModel.date,accountModel.desc,accountModel.joinPerson];
        BOOL res = [_dataBase executeUpdate:sql1];
        
        
         [_dataBase close];
        
        if (!res) {
            NSLog(@"error when insert db table");
            
            return NO;
            
        } else {
            NSLog(@"success to insert db table");
            return YES;
        }
       
        
        
    }
    
    return NO;
}

//修改
-(BOOL)updateId:(NSInteger)index andAccountModel:(AccountModel *)accountModel
{
    if ([_dataBase open]) {
        NSString *updateSql = [NSString stringWithFormat:@"update account set name='%@' fee ='%@' date='%@' desc='%@' joinperson = '%@' where accountId ='%ld'",accountModel.name,accountModel.fee,accountModel.date,accountModel.desc,accountModel.joinPerson,index];
        BOOL res = [_dataBase executeUpdate:updateSql];
        [_dataBase close];
        if (!res) {
            NSLog(@"error when insert db table");
        } else {
            NSLog(@"success to insert db table");
        }
        
        return res;
    }
    return NO;
}

//删除
-(BOOL)deleteWithId:(NSInteger)index
{
    if ([_dataBase open]) {
        NSString *deleteSql = [NSString stringWithFormat:
                               @"delete from account where accountId = '%ld'",
                               index];
        BOOL res = [_dataBase executeUpdate:deleteSql];
        [_dataBase close];
        if (!res) {
            NSLog(@"error when insert db table");
        } else {
            NSLog(@"success to insert db table");
        }
        return res;
    }
    return NO;
}

//删除
-(BOOL)deleteTable{
    
    // 建表语句
    NSString * createTableSql = [NSString stringWithFormat:@"drop table account"];
    // [db executeUpdate:@"create table if not exists t_student (id integer primary key autoincrement, name text not null, age integer)"];
    //创建一个名为User的表，有两个字段分别为string类型的Name，integer类型的 Age
    
    if ([_dataBase open]) {
        
        if ([_dataBase executeUpdate:createTableSql]) {
            
            NSLog(@"删除成功");
            
            return YES;
            
        }
    }
    
    return NO;
    
}

//查询
- (NSArray *)query
{
    NSMutableArray * dataArray = [NSMutableArray array];
    if ([_dataBase open]) {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM account"];
        FMResultSet * rs = [_dataBase executeQuery:sql];
        while ([rs next]) {
            int Id = [rs intForColumn:@"accountId"];
            // name , fee ,date , desc
            NSString * name = [rs stringForColumn:@"name"];
            NSString * fee = [rs stringForColumn:@"fee"];
            NSString * date = [rs stringForColumn:@"date"];
            NSString * desc = [rs stringForColumn:@"desc"];
            NSString * joinPerson = [rs stringForColumn:@"joinperson"];
            AccountModel * accountModel = [[AccountModel alloc] init];
            
            accountModel.name = name;
            accountModel.fee = fee;
            accountModel.date = date;
            accountModel.desc = desc;
            accountModel.joinPerson = joinPerson;
            
            [dataArray addObject:accountModel];
            
            NSLog(@"id = %d, name = %@, fee = %@  date = %@  desc = %@", Id, name, fee, date,desc);
        }
        [_dataBase close];
    }
    
    return dataArray;
}

//条件查询
- (NSArray *)selectTableWithName:(NSString *)name
{
    NSMutableArray * dataArray = [NSMutableArray array];
    if ([_dataBase open]) {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM account where name = '%@'",name];
        FMResultSet * rs = [_dataBase executeQuery:sql];
        while ([rs next]) {
            int Id = [rs intForColumn:@"accountId"];
            // name , fee ,date , desc
            NSString * name = [rs stringForColumn:@"name"];
            NSString * fee = [rs stringForColumn:@"fee"];
            NSString * date = [rs stringForColumn:@"date"];
            NSString * desc = [rs stringForColumn:@"desc"];
            NSString * joinPerson = [rs stringForColumn:@"joinperson"];
            
            AccountModel * accountModel = [[AccountModel alloc] init];
            
            accountModel.name = name;
            accountModel.fee = fee;
            accountModel.date = date;
            accountModel.desc = desc;
            accountModel.joinPerson = joinPerson;
            
            [dataArray addObject:accountModel];
            
            NSLog(@"id = %d, name = %@, fee = %@  date = %@  desc = %@  joinperson = %@"  , Id, name, fee, date,desc,joinPerson);
        }
        [_dataBase close];
    }
    
    return dataArray;
}

//条件查询
- (double )getFeeFromTableWithName:(NSString *)name
{
    double totalFee = 0.0;
    if ([_dataBase open]) {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM account where name = '%@'",name];
        FMResultSet * rs = [_dataBase executeQuery:sql];
        while ([rs next]) {

            NSString * feeString = [rs stringForColumn:@"fee"];
            
            double fee = [feeString doubleValue];
            
            totalFee += fee;

        }
        [_dataBase close];
    }
    
    return totalFee;
}


//计算各人结果
- (NSDictionary *)getEveryOneFeestringFromTable
{
    
    double ztyfee = 0.0;
    double wyfee = 0.0;
    double cwhfee = 0.0;
    double zffee = 0.0;
    double zjfee = 0.0;
    
    double ztytotalfee = 0.0;
    double wytotalfee = 0.0;
    double cwhtotalfee = 0.0;
    double zftotalfee = 0.0;
    double zjtotalfee = 0.0;
    
    double totalFee = 0.0;
    
    NSMutableDictionary * feeDescDict = [NSMutableDictionary dictionary];
    
    if ([_dataBase open]) {
        NSString * sql = [NSString stringWithFormat:
                          @"SELECT * FROM account"];
        FMResultSet * rs = [_dataBase executeQuery:sql];
        while ([rs next]) {
            
            NSString * feeString = [rs stringForColumn:@"fee"];
            
            NSString * joinPersonSting = [rs stringForColumn:@"joinperson"];
            
            NSString * payName = [rs stringForColumn:@"name"];
            
            NSArray * persons = [joinPersonSting componentsSeparatedByString:@","];
            
            NSLog(@"persons==>%@",persons);
            
            double fee = [feeString doubleValue];
            
            double everyFee = fee / persons.count ;
            
            if ([payName isEqualToString:@"张庭勇"]) {
                
                ztytotalfee += fee;
                
            }else if ([payName isEqualToString:@"陈伟豪"]){
                cwhtotalfee += fee;
            }else if ([payName isEqualToString:@"张帆"]){
                zftotalfee += fee;
            }else if ([payName isEqualToString:@"王勇"]){
                wytotalfee += fee;
            }else if ([payName isEqualToString:@"周建"]){
                zjtotalfee += fee;
            }
            
            for (NSString * name in persons) {
                
                if ([name isEqualToString:@"张庭勇"]) {
                    
                    ztyfee += everyFee;
                    
                }else if ([name isEqualToString:@"陈伟豪"]){
                    cwhfee += everyFee;
                }else if ([name isEqualToString:@"张帆"]){
                    zffee += everyFee;
                }else if ([name isEqualToString:@"王勇"]){
                    wyfee += everyFee;
                }else if ([name isEqualToString:@"周建"]){
                    zjfee += everyFee;
                }
                
            }
            
            
            
            totalFee += fee;
            
            
            
            
        }
        
        
        
//        feeDescString = [NSString stringWithFormat:@"张庭勇:%.2f,王勇:%.2f,陈伟豪:%.2f,张帆:%.2f,周建:%.2f",ztyfee,wyfee,cwhfee,zffee,zjfee];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",ztyfee] forKey:@"张庭勇"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",wyfee] forKey:@"王勇"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",cwhfee] forKey:@"陈伟豪"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",zffee] forKey:@"张帆"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",zjfee] forKey:@"周建"];
        
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",ztytotalfee] forKey:@"zty"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",wytotalfee] forKey:@"wy"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",cwhtotalfee] forKey:@"cwh"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",zftotalfee] forKey:@"zf"];
        [feeDescDict setObject:[NSString stringWithFormat:@"%.2f",zjtotalfee] forKey:@"zj"];
        
        
        
        
        NSLog(@"张庭勇==>%.2f--王勇==>%.2f--陈伟豪==>%.2f--张帆==>%.2f--周建==>%.2f",ztyfee,wyfee,cwhfee,zffee,zjfee);
        
        [_dataBase close];
    }
    
    return feeDescDict;
}


@end
